use serde::{Serialize, Deserialize};
use sqlx::Row;

use crate::error::AppErr;

use super::{JtPos, TlPos, get_pool};


pub const SQL_LINKAGE_CREATE: &'static str = r#"
    CREATE TABLE IF NOT EXISTS tb_linkage(
        id INTEGER PRIMARY KEY AUTOINCREMENT, 
        name TEXT UNIQUE NOT NULL, 
        gripper_pos INTEGER NOT NULL, 
        gripper_speed INTEGER NOT NULL, 
        gripper_tor INTEGER NOT NULL
    )
"#;

pub const SQL_LINKAGE_POS_CREATE: &'static str = r#"
    CREATE TABLE IF NOT EXISTS tb_linkage_pos(
        id INTEGER PRIMARY KEY AUTOINCREMENT, 
        linkage_id INTEGER NOT NULL, 
        p1 REAL NOT NULL, 
        p2 REAL NOT NULL, 
        p3 REAL NOT NULL, 
        p4 REAL NOT NULL, 
        p5 REAL NOT NULL, 
        p6 REAL NOT NULL, 
        x REAL NOT NULL, 
        y REAL NOT NULL, 
        z REAL NOT NULL, 
        rx REAL NOT NULL, 
        ry REAL NOT NULL, 
        rz REAL NOT NULL
    )
"#;

#[derive(Debug, Serialize, Deserialize)]
pub struct Gripper {
    pub pos: u8,
    pub speed: u8,
    pub tor: u8,
}


#[derive(Debug, Serialize, Deserialize)]
pub struct Pos {
    pub jt: JtPos,
    pub tl: TlPos,
}

#[derive(Debug, Serialize, Deserialize)]
pub struct Linkage {
    pub name: String,
    pub gripper: Gripper,
    pub pos_list: Box<[Pos]>,
}

#[tauri::command]
pub async fn save_linkage(linkage: Linkage) -> Result<(), AppErr> {

    let mut tx = get_pool().begin().await?;

    let ret = sqlx::query(r#"
        INSERT INTO tb_linkage(name, gripper_pos, gripper_speed, gripper_tor) 
        VALUES(?, ?, ?, ?)
    "#)
    .bind(linkage.name)
    .bind(linkage.gripper.pos)
    .bind(linkage.gripper.speed)
    .bind(linkage.gripper.tor)
    .execute(&mut tx).await?;

    let link_id = ret.last_insert_rowid();

    for pos in linkage.pos_list.iter() {
        sqlx::query(r#"
            INSERT INTO tb_linkage_pos(linkage_id, p1, p2, p3, p4, p5, p6, x, y, z, rx, ry, rz) 
            VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        "#)
        .bind(link_id)
        .bind(pos.jt.p1).bind(pos.jt.p2).bind(pos.jt.p3).bind(pos.jt.p4).bind(pos.jt.p5).bind(pos.jt.p6)
        .bind(pos.tl.x).bind(pos.tl.y).bind(pos.tl.z).bind(pos.tl.rx).bind(pos.tl.ry).bind(pos.tl.rz)
        .execute(&mut tx)
        .await?;
    }

    tx.commit().await?;

    Ok(())
}

#[tauri::command]
pub async fn del_linkage(name: String) -> Result<(), AppErr> {

    let mut tx = get_pool().begin().await?;

    let ret = sqlx::query(r#"
        SELECT id FROM tb_linkage WHERE name = ? LIMIT 1
    "#)
    .bind(name)
    .fetch_one(&mut tx)
    .await?;

    let id: i64 = ret.get(0);

    sqlx::query(r#"
        DELETE FROM tb_linkage WHERE id = ?
    "#)
    .bind(id)
    .execute(&mut tx)
    .await?;

    sqlx::query(r#"
        DELETE FROM tb_linkage_pos WHERE linkage_id = ?
    "#)
    .bind(id)
    .execute(&mut tx)
    .await?;

    tx.commit().await?;

    Ok(())
}

#[tauri::command]
pub async fn get_linkage() -> Result<Box<[Linkage]>, AppErr> {

    let rows = sqlx::query(r#"
        SELECT id, name, gripper_pos, gripper_speed, gripper_tor FROM tb_linkage
    "#)
    .fetch_all( get_pool() )
    .await?;

    let mut vec = Vec::with_capacity( rows.len() );

    for row in rows {
        let id: i64 = row.get(0);
        let name: String = row.get(1);
        let gripper = Gripper {
            pos: row.get(2),
            speed: row.get(3),
            tor: row.get(4),
        };
        let rets = sqlx::query(r#"
            SELECT id, p1, p2, p3, p4, p5, p6, x, y, z, rx, ry, rz FROM tb_linkage_pos 
            WHERE linkage_id = ? order by id asc
        "#)
        .bind(id)
        .fetch_all( get_pool() )
        .await?;
    
        let mut pos_list = Vec::with_capacity( rets.len() );
        for ret in rets {
            let pos = Pos {
                jt: JtPos { p1: ret.get(1), p2: ret.get(2), p3: ret.get(3), p4: ret.get(4), p5: ret.get(5), p6: ret.get(6) },
                tl: TlPos { x: ret.get(7), y: ret.get(8), z: ret.get(9), rx: ret.get(10), ry: ret.get(11), rz: ret.get(12) }
            };
            pos_list.push(pos);
        }
        let linkage = Linkage {
            name,
            gripper,
            pos_list: pos_list.into_boxed_slice(),
        };
        vec.push(linkage);
    }

    Ok( vec.into_boxed_slice() )
}

